package com.example.word_book.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.word_book.bean.ErrorWord;
import com.example.word_book.bean.Lexicon;
import com.example.word_book.bean.Word;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Random;

public class SqlHelp extends SQLiteOpenHelper {
    private SQLiteDatabase db;
    private ContentValues values = new ContentValues();

    public SqlHelp(Context context) {
        super(context, "wordBook.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String createSql = "CREATE TABLE word(id INTEGER primary key AUTOINCREMENT,word  VARCHER(20),type VARCHER(10), translation VARCHER(100), heat INTEGER)";
        sqLiteDatabase.execSQL(createSql);

        createSql = "CREATE TABLE error(id INTEGER primary key AUTOINCREMENT,word  VARCHER(20),date VARCHER(10))";
        sqLiteDatabase.execSQL(createSql);

        createSql = "CREATE TABLE user(user  VARCHER(20),pass VARCHER(10))";
        sqLiteDatabase.execSQL(createSql);

        for (String[] word : Lexicon.LEXICON) {
            values.put("word", word[0]);
            values.put("type", word[1]);
            values.put("translation", word[2]);
            values.put("heat", 0);
            sqLiteDatabase.insert("word", null, values);
            values.clear();
        }

        values.put("user", "admin");
        values.put("pass", "123456");
        sqLiteDatabase.insert("user", null, values);
        values.clear();
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    //根据user和pass在user表里插入一条数据，如果表中有相同的账号，则返回false，返回true则添加成功
    public boolean insertUser(String user, String pass) {
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM user WHERE user = '" + user + "'", null);
        if (cursor.getCount() != 0) {
            db.close();
            return false;
        } else {
            db = this.getWritableDatabase();
            values.put("user", user);
            values.put("pass", pass);
            db.insert("user", null, values);
            values.clear();
            db.close();
            return true;
        }
    }

    //根据输入的user和pass查询表中是否有此数据，若有返回true，若没有返回false
    public boolean selectUserAndPass(String user, String pass) {
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM user WHERE user = '" + user + "' and pass ='" + pass + "'", null);

        if (cursor.getCount() != 0) {
            db.close();
            return true;
        }
        return false;
    }

    public void insertErrorWord(ErrorWord errorWord) {
        db = this.getWritableDatabase();

        values.put("word", errorWord.getWord());
        values.put("date", errorWord.getDate());

        db.insert("error", null, values);
        values.clear();

        db.execSQL("update word set heat = heat + 1 where word = '" + errorWord.getWord() + "'");

        db.close();
    }

    public void deleteExpireError() {
        Date date = new Date();
        System.out.println(date);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");

        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DAY_OF_MONTH, -3);

        db = this.getWritableDatabase();
        db.delete("error", "date <=?", new String[]{simpleDateFormat.format(calendar.getTime())});
        db.close();

    }


    public void deleteErrorWord(String s) {
        db = this.getWritableDatabase();
        db.delete("error", "word=?", new String[]{s});
        db.close();
    }

    public void insertWord(Word word) {
        db = this.getWritableDatabase();
        values.put("word", word.getWord());
        values.put("type", word.getType()[0]);
        values.put("translation", word.getTranslation()[0]);
        values.put("heat", word.getHeat());
        db.insert("word", null, values);
        values.clear();
        db.close();
    }

    public ArrayList<Word> selectErrorWordForDay(Date d) {
        ArrayList<Word> arrayList = new ArrayList<>();
        db = this.getReadableDatabase();
        Cursor cursor = null;
        SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
        if (d != null) {
            String date = format.format(d);
            cursor = db.rawQuery("SELECT * FROM error where date != '" + date + "' order by word ", null);
        } else {
            d = new Date();
            String date = format.format(d);
            cursor = db.rawQuery("SELECT * FROM error where date = '" + date + "' order by word ", null);
        }
        if (cursor.getCount() != 0) {
            cursor.moveToFirst();
            ErrorWord errorWord = new ErrorWord(cursor.getString(1));
            Word word = selectWord(errorWord.getWord());
            arrayList.add(word);
            while (cursor.moveToNext()) {
                if (!arrayList.get(arrayList.size() - 1).getWord().equals(cursor.getString(1))) {
                    word = selectWord(cursor.getString(1));
                    arrayList.add(word);
                }
            }
        }
        cursor.close();
        db.close();
        return arrayList;
    }


    public ArrayList<Word> selectAllWordBySelection(String selection) {
        ArrayList<Word> arrayList = new ArrayList<>();
        db = this.getReadableDatabase();
        Cursor cursor = null;
        if (selection == null)
            cursor = db.query("word", null, null, null, null, null, new String("word"));
        else
//            cursor = db.query("word", null, "word like '%?%'", new String[]{selection}, null, null, new String("word"));
            cursor = db.rawQuery("SELECT * FROM word WHERE word LIKE '%" + selection + "%' order by word", null);

        if (cursor.getCount() != 0) {
            cursor.moveToFirst();
            Word word = new Word(cursor.getInt(0), cursor.getString(1), cursor.getInt(4));
            arrayList.add(word);
            while (cursor.moveToNext()) {
                if (!word.getWord().equals(cursor.getString(1))) {
                    word = new Word(cursor.getInt(0), cursor.getString(1), cursor.getInt(4));
                    arrayList.add(word);
                }
            }
        }
        cursor.close();
        db.close();
        return arrayList;
    }

    public Word selectWord(String wordName) {
        Word word = null;
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM word WHERE word = '" + wordName + "'", null);

        if (cursor.getCount() != 0) {
            cursor.moveToFirst();
            word = new Word(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getInt(4));

            while (cursor.moveToNext()) {
                word.addType(cursor.getString(2));
                word.addTranslation(cursor.getString(3));
            }
        }
        cursor.close();
        db.close();
        return word;
    }


    public String randomWord() {
        String w = "";
        int count = 0;
        Random random = new Random();
        int flag = random.nextInt(100);
        if (flag % 2 == 0) {
            db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery("SELECT count(*) FROM word ", null);
            if (cursor.getCount() != 0) {
                cursor.moveToFirst();
                count = cursor.getInt(0);
            }
            int s = random.nextInt(count + 1);
            cursor = db.rawQuery("SELECT word FROM word where id = " + s, null);
            if (cursor.getCount() != 0) {
                cursor.moveToFirst();
                w = cursor.getString(0);
            }
            cursor.close();
            db.close();
        } else {
            ArrayList<Word> words = selectErrorWordForDay(new Date());
//            ArrayList<Word> words = selectErrorWordForDay(null);
            Collections.shuffle(words);
            if (words.size() == 0)
                w = randomWord();
            else
                w = words.get(0).getWord();
        }

        return w;

    }
}
